package com.zking.service;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.zking.dao.DBPool;
import com.zking.entity.Benefit;
import com.zking.entity.Food;
import com.zking.entity.PageManager;
import com.zking.utils.FileOSSUtils;

public class FoodService {
	private static QueryRunner runner =new QueryRunner(DBPool.getInstance().getDataSource());
	/**
	 * 添加菜品
	 * @param food
	 * @return
	 */
	public int addFood(Food food) {
		
		
		try {
			String sql ="insert into food values (null,?,?,?,null,?,?,?)";
			Object [] params= {food.getFood_name(),food.getFood_price(),food.getFood_introduce(),food.getFood_material(),food.getFood_pic(),food.getStock()};
			return runner.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	/**
	 * 查询的菜品
	 * @param pm
	 * @return
	 */
	public List<Food> queryFoods(PageManager pm){
		
		String sql="select * from food limit ?,?";
		Object [] param = {(pm.getPn()-1)*pm.getLimit(),pm.getLimit()};
		try {
			List<Food> food =runner.query(sql, new BeanListHandler<Food>(Food.class),param);
			return food;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 查询单个菜品
	 * @param f
	 * @return
	 */
	public Food queryFood(Food f) {
		String sql="select * from food where food_id=?";
		Object param [] = {f.getFood_id()};
		try {
			Food food =runner.query(sql, new BeanHandler<Food>(Food.class),param);
			return food ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 更新菜品
	 * @param food
	 * @return
	 * @throws Exception
	 */
	public int updateFood (Food food) throws Exception {
		
		String sql="update food set food_name=?,food_price=?,food_introduce=? ,food_material=?,food_pic=?,stock=? where food_id=?";
		Object params[] = {food.getFood_name(),food.getFood_price(),food.getFood_introduce(), food.getFood_material(),food.getFood_pic(),food.getStock(),food.getFood_id()};
		Food food2 =queryFood(food);
		FileOSSUtils fileOssUtils=new FileOSSUtils();
		fileOssUtils.deleteFile(food2.getFood_pic());
		try {
			return runner.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	/**
	 * 删除菜品
	 * @param food
	 * @return
	 */
	
	public int deleteFood (Food food) {
		
	
		
		try {
			FileOSSUtils fileOssUtils=new FileOSSUtils();
			Object param[]= {food.getFood_id()};
			String sql1="select * from where food_id= ?";
			
			Food food2 =runner.query(sql1, new BeanHandler<Food>(Food.class));
			fileOssUtils.deleteFile(food2.getFood_pic());
			
			String sql ="delete from food where food_id=?";
		
			
		
			return runner.update(sql,param);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
		
	}
	
	/**
	 * 获取数据库food 表的数据条数
	 * @return
	 */
	public int getTotalCount() {
		String sql ="select count(*) from food";
		try {
			return Integer.parseInt(runner.query(sql, new ScalarHandler<Object>()).toString());
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	/**
	 * 获取所有的菜品
	 * @return
	 */
	
	public List<Food> getAllFood(){
		String sql="select * from food";
		try {
			return runner.query(sql, new BeanListHandler<Food>(Food.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		}
	
	/**
	 * 模糊查询 菜品
	 * @param food
	 * @return
	 */
	
	public List<Food> searchFood(Food food ){
		String sql="select * from food where food_name like ? ";
		Object param[]={"%"+food.getFood_name()+"%"};
		try {
			return runner.query(sql, new BeanListHandler<Food>(Food.class),param);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 存储每日特惠菜品
	 * @param benefit
	 * @return
	 * @throws SQLException
	 */
	
	public int pubishBenefit(Benefit benefit) throws SQLException {
		String sql="insert into benefit values(NULL,?,?,?)";
		Object params []= {benefit.getBenefit_foods(),benefit.getAddTime(),benefit.getDiscount()};
		
		return runner.update(sql,params);
		
	}
	/**
	 * 获取所有特惠菜品
	 * @return
	 * @throws SQLException 
	 */
	public List<Food> benefitFoods() throws SQLException{
		Date t = new Date();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String nowTime=df.format(t);
		String sql ="select benefit_foods  from benefit where addTime=?";
		Object param[] = {nowTime};
		Benefit benefit =runner.query(sql, new BeanHandler<Benefit>(Benefit.class),param);
		String  foodNames[]= benefit.getBenefit_foods().split(",");
		Object params[]=foodNames;
		String sql2 = "select * from food where food_name in (";
		for (int i = 0; i <= foodNames.length-1; i++) {

			if(i==foodNames.length-1) {
				sql2 +="? )";
			}
			else {
				sql2 +="? ,";
			}
		}
		
		
         return runner.query(sql2, new BeanListHandler<Food>(Food.class),params);
	
	}
//	public static void main(String[] args) throws SQLException {
//		
//
//		FoodService foodService=new FoodService();
//		try {
//			List<Food> food =foodService.benefitFoods();
//			System.out.println(food);
//			System.out.println(food.size());
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	}
	
}
